-- RiceShop 微信支付模块数据库迁移脚本
-- 版本: v1.0
-- 日期: 2024年

-- 1. 创建支付记录表
CREATE TABLE PaymentRecord (
    PaymentId INT IDENTITY(1,1) PRIMARY KEY,
    OrderId INT NOT NULL,
    OrderNo NVARCHAR(50) NOT NULL,
    PaymentNo NVARCHAR(50) NOT NULL,
    PrepayId NVARCHAR(50) NULL,
    TransactionId NVARCHAR(50) NULL,
    PaymentAmount DECIMAL(10,2) NOT NULL,
    PaymentStatus INT NOT NULL DEFAULT 0,
    PaymentMethod NVARCHAR(20) NOT NULL DEFAULT 'WXPAY',
    PaymentTime DATETIME NULL,
    FailReason NVARCHAR(200) NULL,
    CreateTime DATETIME NOT NULL DEFAULT GETDATE(),
    UpdateTime DATETIME NOT NULL DEFAULT GETDATE()
);

-- 2. 创建退款记录表
CREATE TABLE RefundRecord (
    RefundId INT IDENTITY(1,1) PRIMARY KEY,
    OrderId INT NOT NULL,
    PaymentId INT NOT NULL,
    OrderNo NVARCHAR(50) NOT NULL,
    RefundNo NVARCHAR(50) NOT NULL,
    WxRefundId NVARCHAR(50) NULL,
    RefundAmount DECIMAL(10,2) NOT NULL,
    RefundStatus INT NOT NULL DEFAULT 0,
    RefundReason NVARCHAR(200) NULL,
    RefundTime DATETIME NULL,
    FailReason NVARCHAR(200) NULL,
    CreateTime DATETIME NOT NULL DEFAULT GETDATE(),
    UpdateTime DATETIME NOT NULL DEFAULT GETDATE()
);

-- 3. 创建索引
-- 支付记录表索引
CREATE INDEX IX_PaymentRecord_OrderNo ON PaymentRecord(OrderNo);
CREATE INDEX IX_PaymentRecord_PaymentNo ON PaymentRecord(PaymentNo);
CREATE INDEX IX_PaymentRecord_PaymentStatus ON PaymentRecord(PaymentStatus);
CREATE INDEX IX_PaymentRecord_CreateTime ON PaymentRecord(CreateTime);
CREATE INDEX IX_PaymentRecord_OrderId ON PaymentRecord(OrderId);

-- 退款记录表索引
CREATE INDEX IX_RefundRecord_RefundNo ON RefundRecord(RefundNo);
CREATE INDEX IX_RefundRecord_OrderNo ON RefundRecord(OrderNo);
CREATE INDEX IX_RefundRecord_PaymentId ON RefundRecord(PaymentId);
CREATE INDEX IX_RefundRecord_RefundStatus ON RefundRecord(RefundStatus);
CREATE INDEX IX_RefundRecord_CreateTime ON RefundRecord(CreateTime);

-- 4. 创建外键约束
-- 支付记录表外键
ALTER TABLE PaymentRecord 
ADD CONSTRAINT FK_PaymentRecord_Order 
FOREIGN KEY (OrderId) REFERENCES [Order](Id);

-- 退款记录表外键
ALTER TABLE RefundRecord 
ADD CONSTRAINT FK_RefundRecord_Order 
FOREIGN KEY (OrderId) REFERENCES [Order](Id);

ALTER TABLE RefundRecord 
ADD CONSTRAINT FK_RefundRecord_PaymentRecord 
FOREIGN KEY (PaymentId) REFERENCES PaymentRecord(PaymentId);

-- 5. 添加约束
-- 支付记录表约束
ALTER TABLE PaymentRecord 
ADD CONSTRAINT CK_PaymentRecord_PaymentStatus 
CHECK (PaymentStatus IN (0, 1, 2, 3, 4));

ALTER TABLE PaymentRecord 
ADD CONSTRAINT CK_PaymentRecord_PaymentAmount 
CHECK (PaymentAmount > 0);

-- 退款记录表约束
ALTER TABLE RefundRecord 
ADD CONSTRAINT CK_RefundRecord_RefundStatus 
CHECK (RefundStatus IN (0, 1, 2, 3));

ALTER TABLE RefundRecord 
ADD CONSTRAINT CK_RefundRecord_RefundAmount 
CHECK (RefundAmount > 0);

-- 6. 创建唯一约束
ALTER TABLE PaymentRecord 
ADD CONSTRAINT UQ_PaymentRecord_PaymentNo 
UNIQUE (PaymentNo);

ALTER TABLE RefundRecord 
ADD CONSTRAINT UQ_RefundRecord_RefundNo 
UNIQUE (RefundNo);

-- 7. 添加注释
EXEC sp_addextendedproperty 
    @name = N'MS_Description', 
    @value = N'支付记录表', 
    @level0type = N'SCHEMA', @level0name = N'dbo', 
    @level1type = N'TABLE', @level1name = N'PaymentRecord';

EXEC sp_addextendedproperty 
    @name = N'MS_Description', 
    @value = N'退款记录表', 
    @level0type = N'SCHEMA', @level0name = N'dbo', 
    @level1type = N'TABLE', @level1name = N'RefundRecord';

-- 8. 创建视图
-- 支付统计视图
CREATE VIEW vw_PaymentStatistics AS
SELECT 
    CAST(CreateTime AS DATE) AS PaymentDate,
    PaymentMethod,
    COUNT(*) AS TotalCount,
    SUM(CASE WHEN PaymentStatus = 1 THEN 1 ELSE 0 END) AS SuccessCount,
    SUM(CASE WHEN PaymentStatus = 2 THEN 1 ELSE 0 END) AS FailedCount,
    SUM(PaymentAmount) AS TotalAmount,
    SUM(CASE WHEN PaymentStatus = 1 THEN PaymentAmount ELSE 0 END) AS SuccessAmount,
    SUM(CASE WHEN PaymentStatus = 2 THEN PaymentAmount ELSE 0 END) AS FailedAmount,
    CAST(SUM(CASE WHEN PaymentStatus = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS DECIMAL(5,2)) AS SuccessRate
FROM PaymentRecord
GROUP BY CAST(CreateTime AS DATE), PaymentMethod;

-- 退款统计视图
CREATE VIEW vw_RefundStatistics AS
SELECT 
    CAST(r.CreateTime AS DATE) AS RefundDate,
    COUNT(*) AS RefundCount,
    SUM(r.RefundAmount) AS RefundAmount,
    SUM(CASE WHEN r.RefundStatus = 1 THEN 1 ELSE 0 END) AS SuccessRefundCount,
    SUM(CASE WHEN r.RefundStatus = 1 THEN r.RefundAmount ELSE 0 END) AS SuccessRefundAmount,
    CAST(SUM(CASE WHEN r.RefundStatus = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS DECIMAL(5,2)) AS RefundSuccessRate
FROM RefundRecord r
GROUP BY CAST(r.CreateTime AS DATE);

-- 9. 创建存储过程
-- 获取支付统计数据的存储过程
CREATE PROCEDURE sp_GetPaymentStatistics
    @StartDate DATETIME,
    @EndDate DATETIME
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 支付成功率
    SELECT 
        COUNT(*) AS TotalPayments,
        SUM(CASE WHEN PaymentStatus = 1 THEN 1 ELSE 0 END) AS SuccessPayments,
        CAST(SUM(CASE WHEN PaymentStatus = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS DECIMAL(5,2)) AS SuccessRate
    FROM PaymentRecord 
    WHERE CreateTime BETWEEN @StartDate AND @EndDate;
    
    -- 支付金额统计
    SELECT 
        SUM(PaymentAmount) AS TotalAmount,
        SUM(CASE WHEN PaymentStatus = 1 THEN PaymentAmount ELSE 0 END) AS SuccessAmount,
        SUM(CASE WHEN PaymentStatus = 2 THEN PaymentAmount ELSE 0 END) AS FailedAmount
    FROM PaymentRecord 
    WHERE CreateTime BETWEEN @StartDate AND @EndDate;
    
    -- 退款统计
    SELECT 
        COUNT(*) AS TotalRefunds,
        SUM(RefundAmount) AS TotalRefundAmount,
        SUM(CASE WHEN RefundStatus = 1 THEN 1 ELSE 0 END) AS SuccessRefunds,
        SUM(CASE WHEN RefundStatus = 1 THEN RefundAmount ELSE 0 END) AS SuccessRefundAmount
    FROM RefundRecord 
    WHERE CreateTime BETWEEN @StartDate AND @EndDate;
END;

-- 10. 插入初始数据（可选）
-- 这里可以插入一些测试数据或者配置数据

-- 11. 创建触发器
-- 支付记录更新时自动更新UpdateTime
CREATE TRIGGER tr_PaymentRecord_UpdateTime
ON PaymentRecord
AFTER UPDATE
AS
BEGIN
    UPDATE PaymentRecord 
    SET UpdateTime = GETDATE()
    FROM PaymentRecord p
    INNER JOIN inserted i ON p.PaymentId = i.PaymentId;
END;

-- 退款记录更新时自动更新UpdateTime
CREATE TRIGGER tr_RefundRecord_UpdateTime
ON RefundRecord
AFTER UPDATE
AS
BEGIN
    UPDATE RefundRecord 
    SET UpdateTime = GETDATE()
    FROM RefundRecord r
    INNER JOIN inserted i ON r.RefundId = i.RefundId;
END;

-- 12. 创建函数
-- 获取支付状态描述的函数
CREATE FUNCTION fn_GetPaymentStatusDesc(@Status INT)
RETURNS NVARCHAR(20)
AS
BEGIN
    DECLARE @StatusDesc NVARCHAR(20);
    
    SET @StatusDesc = CASE @Status
        WHEN 0 THEN N'待支付'
        WHEN 1 THEN N'支付成功'
        WHEN 2 THEN N'支付失败'
        WHEN 3 THEN N'已关闭'
        WHEN 4 THEN N'已退款'
        ELSE N'未知状态'
    END;
    
    RETURN @StatusDesc;
END;

-- 获取退款状态描述的函数
CREATE FUNCTION fn_GetRefundStatusDesc(@Status INT)
RETURNS NVARCHAR(20)
AS
BEGIN
    DECLARE @StatusDesc NVARCHAR(20);
    
    SET @StatusDesc = CASE @Status
        WHEN 0 THEN N'处理中'
        WHEN 1 THEN N'退款成功'
        WHEN 2 THEN N'退款失败'
        WHEN 3 THEN N'已撤销'
        ELSE N'未知状态'
    END;
    
    RETURN @StatusDesc;
END;

-- 迁移完成
PRINT '微信支付模块数据库迁移完成！';
PRINT '已创建以下对象：';
PRINT '- PaymentRecord 表（支付记录表）';
PRINT '- RefundRecord 表（退款记录表）';
PRINT '- 相关索引和约束';
PRINT '- 统计视图和存储过程';
PRINT '- 触发器和函数'; 